/**
 *  Copyright 2010 ShenYang voole Co. Ltd.
 *  All right reserved. 
 */
package com.dianning.dao.hibernate;


import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.dianning.bean.AdAds;
import com.dianning.bean.AdSpace;
import com.dianning.bean.Admin;
import com.dianning.bean.Area;
import com.dianning.bean.Channel;
import com.dianning.bean.Credit;
import com.dianning.bean.Info;
import com.dianning.bean.Visitor;
import com.dianning.comm.Constant;
import com.dianning.comm.OrderObj;
import com.dianning.dao.AdAdsDAO;
import com.dianning.dao.AdSpaceDAO;
import com.dianning.dao.AdminDAO;
import com.dianning.dao.AreaDAO;
import com.dianning.dao.ChannelDAO;
import com.dianning.dao.CreditDAO;
import com.dianning.dao.InfoDAO;
import com.dianning.dao.VisitorDAO;

/**
 * 功能描述:信息操作实现
 *
 * 创建日期 Apr 10, 2010
 *
 * @author zhangbaochao
 *
 * @version 1.0
 *
 */
public class InfoHibernateDAO extends HibernateDaoSupport implements InfoDAO {

	private static final String LOAD_BY_ID = "from Info where id = ?";
	private static final String UPDATE_CITYTOP_TIME = "update Info set isCityTop=0 where isCityTop=1 and cityTopTime<getdate()";
	private static final String UPDATE_AREATOP_TIME = "update Info set isAreaTop=0 where isAreaTop=1 and areaTopTime<getdate()";
	private static final String UPDATE_COMMEND_TIME = "update Info set isCommend=0 where isCommend=1 and commendTime<getdate()";
	
	/**
	 * 保存Info对象
	 * 
	 * @param info
	 * 			Info对象
	 * @return Info
	 * 			Info对象
	 */
	public Info saveInfo(Info info){
		this.getHibernateTemplate().save(info);
		return info;
	}
	/**
	 * 保存或更新Info对象
	 * 
	 * @param info
	 * 			Info对象
	 * @return Info
	 * 			Info对象
	 */
	public Info saveOrUpdateInfo(Info info){
		this.getHibernateTemplate().saveOrUpdate(info);
		return info;
	}
	/**
	 * 保存Info对象
	 * 
	 * @param infoinfo
	 * 			Info对象
	 * @return Info
	 * 			Info对象
	 */
	public Info updateInfo(Info info){
		this.getHibernateTemplate().update(info);
		return info;
	}
	
	/**
	 * 更新置顶时间
	 */
	public void updateCityTopInfo(){
		getHibernateTemplate().execute(new HibernateCallback() {
		      public Object doInHibernate(Session s) throws HibernateException, SQLException {
		        Query query = s.createQuery(UPDATE_CITYTOP_TIME);
		        query.executeUpdate();
		        return null;
		      }
		    });
	}
	
	/**
	 * 更新置顶时间
	 */
	public void updateAreaTopInfo(){
		getHibernateTemplate().execute(new HibernateCallback() {
		      public Object doInHibernate(Session s) throws HibernateException, SQLException {
		        Query query = s.createQuery(UPDATE_AREATOP_TIME);
		        query.executeUpdate();
		        return null;
		      }
		    });
	}
	
	/**
	 * 更新置顶时间
	 */
	public void updateCommendInfo(){
		getHibernateTemplate().execute(new HibernateCallback() {
		      public Object doInHibernate(Session s) throws HibernateException, SQLException {
		        Query query = s.createQuery(UPDATE_COMMEND_TIME);
		        query.executeUpdate();
		        return null;
		      }
		    });
	}
	
	/**
	 * 根据主键ID获取Info对象
	 * 
	 * @param id
	 * 			主键ID
	 * @return Info
	 * 			Info对象
	 */
	public Info findInfoById(long id){
		List l = this.getHibernateTemplate().find(LOAD_BY_ID,id);
		if(l == null || l.isEmpty()){
			return null;
		}else{
			return (Info) l.get(0);
		}
	}
	
	/**
	 * 获取Info个数
	 * 
	 * @return int
	 * 			Info个数
	 */
	public long getInfoNum(final long uid,final long cid,final long ccid,final long areaId,final long tradingareaId,final int isbiz,final int validated,final int status,final int isprofile,final String title,final List wherein){
		StringBuffer sb = new StringBuffer();
		sb.append("select count(id) from Info where 1=1");
		if (uid != -1) {
			sb.append(" and uid = ");
			sb.append(uid);
		}
		if (cid != -1) {
			sb.append(" and cid = ");
			sb.append(cid);
		}
		if (ccid != -1) {
			sb.append(" and ccid = ");
			sb.append(ccid);
		}
		if (areaId != -1) {
			sb.append(" and areaId = ");
			sb.append(areaId);
		}
		if (tradingareaId != -1) {
			sb.append(" and tradingareaId = ");
			sb.append(tradingareaId);
		}
		if (isbiz != -1) {
			sb.append(" and isbiz = ");
			sb.append(isbiz);
		}
		if (validated != -1) {
			sb.append(" and validated = ");
			sb.append(validated);
		}
		if (status != -1) {
			sb.append(" and status = ");
			sb.append(status);
		}
		if (isprofile != -1) {
			sb.append(" and isprofile = ");
			sb.append(isprofile);
		}
		if(StringUtils.isNotBlank(title)){
			sb.append(" and title like '%");
			sb.append(title);
			sb.append("%'");
		}
		if(wherein!=null && !wherein.isEmpty()){
			for(int i=0;i<wherein.size();i++){
				String where = (String) wherein.get(i);
				sb.append(" and id in(");
				sb.append("select infoId from InfoExpand where ");
				sb.append(where);
				sb.append(")");
			}
		}
		List l = this.getHibernateTemplate().find(sb.toString());
		
		if (l == null || l.isEmpty()) {
			return 0;
		} else {
			return (Long) l.get(0);
		}
	}
	
	/**
	 * 获取Info列表
	 * 
	 * @param orderby
	 *            String
	 * @param ascOrDesc
	 *            int
	 * @param firstResult
	 *            int
	 * @param maxResults
	 *            int
	 * @return List
	 * 			Info对象列表
	 */
	public List findInfos(final long uid,final long cid,final long ccid,final long areaId,final long tradingareaId,final int isbiz,final int validated,final int status,final int isprofile,final String title,final List ids,final OrderObj[] oo){
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException {
				Criteria c = s.createCriteria(Info.class);
				if (uid != -1) {
					c.add(Restrictions.eq("uid", new Long(uid)));
				}
				if (cid != -1) {
					c.add(Restrictions.eq("cid", new Long(cid)));
				}
				if (ccid != -1) {
					c.add(Restrictions.eq("ccid", new Long(ccid)));
				}
				if (areaId != -1) {
					c.add(Restrictions.eq("areaId", new Long(areaId)));
				}
				if (tradingareaId != -1) {
					c.add(Restrictions.eq("tradingareaId", new Long(tradingareaId)));
				}
				if (isbiz != -1) {
					c.add(Restrictions.eq("isbiz", new Integer(isbiz)));
				}
				if(validated != -1){
					c.add(Restrictions.eq("validated", new Integer(validated)));
				}
				if(status != -1){
					c.add(Restrictions.eq("status", new Integer(status)));
				}
				if (isprofile != -1) {
					c.add(Restrictions.eq("isProfile", new Integer(isprofile)));
				}
				if(StringUtils.isNotBlank(title)){
					c.add(Restrictions.like("title", "%" + title + "%"));
				}
				if(ids != null && !ids.isEmpty()){
					c.add(Restrictions.in("id", ids.toArray()));
				}
				if (oo != null && oo.length > 0) {
					for (int i = 0; i < oo.length; i++) {
						if (StringUtils.isNotBlank(oo[i].getOrderBy())) {
							if (oo[i].getAscOrDesc() == Constant.ORDER_ASC) {
								c.addOrder(Order.asc(oo[i].getOrderBy()));
							}
							if (oo[i].getAscOrDesc() == Constant.ORDER_DESC) {
								c.addOrder(Order.desc(oo[i].getOrderBy()));
							}
						}
					}
				}
				return c.list();
			}
		});
	}
	
	/**
	 * 获取Info列表
	 * 
	 * @param orderby
	 *            String
	 * @param ascOrDesc
	 *            int
	 * @param firstResult
	 *            int
	 * @param maxResults
	 *            int
	 * @return List
	 * 			Info对象列表
	 */
	public List findInfos(final long uid,final long cid,final long ccid,final long areaId,final long tradingareaId,final int isbiz,final int validated,final int status,final int isprofile,final String title,final List wherein,final OrderObj[] oo, final int firstResult, final int maxResults){
		StringBuffer sb = new StringBuffer();
		sb.append("from Info where 1=1");
		if (uid != -1) {
			sb.append(" and uid = ");
			sb.append(uid);
		}
		if (cid != -1) {
			sb.append(" and cid = ");
			sb.append(cid);
		}
		if (ccid != -1) {
			sb.append(" and ccid = ");
			sb.append(ccid);
		}
		if (areaId != -1) {
			sb.append(" and areaId = ");
			sb.append(areaId);
		}
		if (tradingareaId != -1) {
			sb.append(" and tradingareaId = ");
			sb.append(tradingareaId);
		}
		if (isbiz != -1) {
			sb.append(" and isbiz = ");
			sb.append(isbiz);
		}
		if (validated != -1) {
			sb.append(" and validated = ");
			sb.append(validated);
		}
		if (status != -1) {
			sb.append(" and status = ");
			sb.append(status);
		}
		if (isprofile != -1) {
			sb.append(" and isprofile = ");
			sb.append(isprofile);
		}
		if(StringUtils.isNotBlank(title)){
			sb.append(" and title like '%");
			sb.append(title);
			sb.append("%'");
		}
		if(wherein!=null && !wherein.isEmpty()){
			for(int i=0;i<wherein.size();i++){
				String where = (String) wherein.get(i);
				sb.append(" and id in(");
				sb.append("select infoId from InfoExpand where ");
				sb.append(where);
				sb.append(")");
			}
		}
		if (oo != null && oo.length > 0) {
			sb.append(" order by ");
			for (int i = 0; i < oo.length; i++) {
				if(i!=0){
					sb.append(",");
				}
				if (StringUtils.isNotBlank(oo[i].getOrderBy())) {
					if (oo[i].getAscOrDesc() == Constant.ORDER_ASC) {
						sb.append(oo[i].getOrderBy()+" asc");
					}
					if (oo[i].getAscOrDesc() == Constant.ORDER_DESC) {
						sb.append(oo[i].getOrderBy()+" desc");
					}
				}
			}
		}
		final String sql = sb.toString();
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException, SQLException {
				Query query = s.createQuery(sql);
				query.setFirstResult(firstResult);
				query.setMaxResults(maxResults);
				List list = query.list();
				return list;
			}
		});
	}
	
	/**
	 * 获取Info列表
	 * @param id
	 * @param ccid
	 * @param validated
	 * @param status
	 * @param orderby
	 * @param ascOrDesc
	 * @param firstResult
	 * @param maxResults
	 * @return List
	 * 			Info对象列表
	 */
	public List findInfos(final long id,final long uid,final long ccid,final int validated,final int status,final int isprofile,final String orderby, final int ascOrDesc, final int firstResult, final int maxResults){
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException {
				Criteria c = s.createCriteria(Info.class);
				if (id != -1) {
					c.add(Restrictions.ne("id", new Long(id)));
				}
				if (uid != -1) {
					c.add(Restrictions.eq("uid", new Long(uid)));
				}
				if (ccid != -1) {
					c.add(Restrictions.eq("ccid", new Long(ccid)));
				}
				if(validated != -1){
					c.add(Restrictions.eq("validated", new Integer(validated)));
				}
				if(status != -1){
					c.add(Restrictions.eq("status", new Integer(status)));
				}
				if (isprofile != -1) {
					c.add(Restrictions.eq("isProfile", new Integer(isprofile)));
				}
				if (StringUtils.isNotBlank(orderby)) {
					if (ascOrDesc == Constant.ORDER_ASC) {
						c.addOrder(Order.asc(orderby));
					}
					if (ascOrDesc == Constant.ORDER_DESC) {
						c.addOrder(Order.desc(orderby));
					}
				}
				c.setFirstResult(firstResult);
				c.setMaxResults(maxResults);
				return c.list();
			}
		});
	}
	
	/**
	 * @param ccid
	 * @param isCommend
	 * @return long
	 * 		Info个数
	 */
	public long getCommendInfoNum(long ccid,int isCommend){
		StringBuffer sb = new StringBuffer();
		sb.append("select count(id) from Info where 1=1");
		if (ccid != -1) {
			sb.append(" and ccid = ");
			sb.append(ccid);
		}
		if (isCommend != -1) {
			sb.append(" and isCommend = ");
			sb.append(isCommend);
		}
		List l = this.getHibernateTemplate().find(sb.toString());
		
		if (l == null || l.isEmpty()) {
			return 0;
		} else {
			return (Long) l.get(0);
		}
	}
	
	/**
	 * 根据频道代码获取信息个数
	 * @param code
	 * @return
	 */
	public long getInfoNumByCid(long cid,int validated){
		StringBuffer sb = new StringBuffer();
		sb.append("select count(id) from Info where cid="+cid);
		if (validated != -1) {
			sb.append(" and validated = ");
			sb.append(validated);
		}
		List l = getHibernateTemplate().find(sb.toString());
		if (l == null || l.isEmpty()) {
			return 0;
		} else {
			return (Long) l.get(0);
		}
	}
}
